/* 
 * Copyright (C) 2009 Roman Masek
 * 
 * This file is part of OpenSudoku.
 * 
 * OpenSudoku is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * OpenSudoku is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with OpenSudoku.  If not, see <http://www.gnu.org/licenses/>.
 * 
 */

package com.lanou.renrenxin.gamemodel.gamethree.db;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.lanou.renrenxin.R;
import com.lanou.renrenxin.gamemodel.gamethree.game.SudokuGame;

/**
 * This class helps open, create, and upgrade the database file.
 */
public class DatabaseHelper extends SQLiteOpenHelper {

	private static final String TAG = "DatabaseHelper";
	
	public static final int DATABASE_VERSION = 8;
	
	private Context mContext;
	
    DatabaseHelper(Context context) {
        super(context, SudokuDatabase.DATABASE_NAME, null, DATABASE_VERSION);
        this.mContext = context;
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("CREATE TABLE " + SudokuDatabase.SUDOKU_TABLE_NAME + " ("
                + SudokuColumns._ID + " INTEGER PRIMARY KEY,"
                + SudokuColumns.FOLDER_ID + " INTEGER,"
                + SudokuColumns.CREATED + " INTEGER,"
                + SudokuColumns.STATE + " INTEGER,"
                + SudokuColumns.TIME + " INTEGER,"
                + SudokuColumns.LAST_PLAYED + " INTEGER,"
                + SudokuColumns.DATA + " Text,"
                + SudokuColumns.PUZZLE_NOTE + " Text"
                + ");");
        
        db.execSQL("CREATE TABLE " + SudokuDatabase.FOLDER_TABLE_NAME + " ("
                + FolderColumns._ID + " INTEGER PRIMARY KEY,"
                + SudokuColumns.CREATED + " INTEGER,"
                + FolderColumns.NAME + " TEXT"
                + ");");
        
        insertFolder(db,1,mContext.getString(R.string.difficulty_easy));
        insertSudoku(db,1,1,"Easy1","052006000160900004049803620400000800083201590001000002097305240200009056000100970");
        insertSudoku(db,1,2,"Easy2","052400100100002030000813025400007010683000597070500002890365000010700006006004970");
        insertSudoku(db,1,3,"Easy3","302000089068052734009000000400007000083201590000500002000000200214780350530000908");
        insertSudoku(db,1,4,"Easy4","402000007000080420050302006090030050503060708070010060900406030015070000200000809");
        insertSudoku(db,1,5,"Easy5","060091080109680405050040106600000200023904710004000003907020030305079602040150070");
        insertSudoku(db,1,6,"Easy6","060090380009080405050300106001008200020904010004200900907006030305070600046050070");
        insertSudoku(db,1,7,"Easy7","402000380109607400008300106090030004023964710800010060907006500005809602046000809");
        insertSudoku(db,1,8,"Easy8","400091000009007425058340190691000000003964700000000963087026530315800600000150009");
        insertSudoku(db,1,9,"Easy9","380001004002600070000487003000040239201000406495060000600854000070006800800700092");
        insertSudoku(db,1,10,"Easy10","007520060002009008006407000768005009031000450400300781000804300100200800050013600");
        insertSudoku(db,1,11,"Easy11","380000000540009078000407503000145209000908000405362000609804000170200045000000092");
        insertSudoku(db,1,12,"Easy12","007001000540609078900487000760100230230000056095002081000854007170206045000700600");
        insertSudoku(db,1,13,"Easy13","007021900502009078006407500000140039031908450490062000009804300170200805004710600");
        insertSudoku(db,1,14,"Easy14","086500204407008090350009000009080601010000080608090300000200076030800409105004820");
        insertSudoku(db,1,15,"Easy15","086507000007360100000000068249003050500000007070100342890000000002056400000904820");
        insertSudoku(db,1,16,"Easy16","000007230420368000050029768000080650000602000078090000894230070000856019065900000");
        insertSudoku(db,1,17,"Easy17","906000200400368190350400000209080051013040980670090302000001076032856009005000803");
        insertSudoku(db,1,18,"Easy18","095002000700804001810076500476000302000000000301000857003290075500307006000400130");
        insertSudoku(db,1,19,"Easy19","005002740002850901810000500070501302008723600301609050003000075509017200087400100");
        insertSudoku(db,1,20,"Easy20","605102740732004001000000020400501300008020600001609007060000000500300286087405109");
        insertSudoku(db,1,21,"Easy21","695102040700800000000970023076000090900020004020000850160098000000007006080405139");
        insertSudoku(db,1,22,"Easy22","090002748000004901800906500470500090008000600020009057003208005509300000287400030");
        insertSudoku(db,1,23,"Easy23","001009048089070030003106005390000500058602170007000094900708300030040860870300400");
        insertSudoku(db,1,24,"Easy24","600039708000004600000100025002017506408000103107850200910008000005900000806320009");
        insertSudoku(db,1,25,"Easy25","620500700500270631040100005302000086000090000160000204900008050235041007006005019");
        insertSudoku(db,1,26,"Easy26","080130002140902007273080000000070206007203900502040000000060318600308024400021050");
        insertSudoku(db,1,27,"Easy27","980100402046950000200684001010009086007000900590800070700465008000098720408001059");
        insertSudoku(db,1,28,"Easy28","085100400000950007073684001010070080067203940090040070700465310600098000008001650");
        insertSudoku(db,1,29,"Easy29","085100460146000807070004001300009080067000940090800003700400010601000724038001650");
        insertSudoku(db,1,30,"Easy30","085130462006000007270680090000009200060213040002800000020065018600000700438021650");
        insertFolder(db,2,mContext.getString(R.string.difficulty_medium));
        insertSudoku(db,2,31,"Medium1","916004072800620050500008930060000200000207000005000090097800003080076009450100687");
        insertSudoku(db,2,32,"Medium2","000900082063001409908000000000670300046050290007023000000000701704300620630007000");
        insertSudoku(db,2,33,"Medium3","035670000400829500080003060020005807800206005301700020040900070002487006000052490");
        insertSudoku(db,2,34,"Medium4","030070902470009000009003060024000837007000100351000620040900200000400056708050090");
        insertSudoku(db,2,35,"Medium5","084200000930840000057000000600401700400070002005602009000000980000028047000003210");
        insertSudoku(db,2,36,"Medium6","007861000008003000560090010100070085000345000630010007050020098000600500000537100");
        insertSudoku(db,2,37,"Medium7","040001003000050079560002804100270080082000960030018007306100098470080000800500040");
        insertSudoku(db,2,38,"Medium8","000500006000870302270300081000034900793050614008790000920003057506087000300005000");
        insertSudoku(db,2,39,"Medium9","000900067090000208460078000320094070700603002010780043000850016501000090670009000");
        insertSudoku(db,2,40,"Medium10","024000017000301000300000965201000650000637000093000708539000001000502000840000570");
        insertSudoku(db,2,41,"Medium11","200006143004000600607008029100800200003090800005003001830500902006000400942600005");
        insertSudoku(db,2,42,"Medium12","504002030900073008670000020000030780005709200047060000050000014100450009060300502");
        insertSudoku(db,2,43,"Medium13","580000637000000000603540000090104705010709040807205090000026304000000000468000072");
        insertSudoku(db,2,44,"Medium14","000010000900003408670500021000130780015000240047065000750006014102400009000090000");
        insertSudoku(db,2,45,"Medium15","780300050956000000002065001003400570600000003025008100200590800000000417030004025");
        insertSudoku(db,2,46,"Medium16","200367500500800060300450700090530400080000070003074050001026005030005007002783001");
        insertSudoku(db,2,47,"Medium17","801056200000002381900003000350470000008000100000068037000600002687100000004530806");
        insertSudoku(db,2,48,"Medium18","300004005841753060000010000003000087098107540750000100000070000030281796200300008");
        insertSudoku(db,2,49,"Medium19","000064810040050062009010300003040607008107500704030100006070200430080090017390000");
        insertSudoku(db,2,50,"Medium20","000040320000357080000600400357006040600705003080900675008009000090581000064070000");
        insertSudoku(db,2,51,"Medium21","905040026026050900030600050350000009009020800100000075010009030003080760560070108");
        insertSudoku(db,2,52,"Medium22","010403060030017400200000300070080004092354780500070030003000005008530040050906020");
        insertSudoku(db,2,53,"Medium23","605900100000100073071300005009010004046293510700040600200001730160002000008009401");
        insertSudoku(db,2,54,"Medium24","049060002800210490100040000000035084008102300630470000000080001084051006700020950");
        insertSudoku(db,2,55,"Medium25","067020300003700000920103000402035060300000002010240903000508039000009200008010750");
        insertSudoku(db,2,56,"Medium26","050842001004000900800050040600400019007506800430009002080090006001000400500681090");
        insertSudoku(db,2,57,"Medium27","000076189000002030009813000025000010083000590070000460000365200010700000536120000");
        insertSudoku(db,2,58,"Medium28","080000030400368000350409700000003650003000900078100000004201076000856009060000020");
        insertSudoku(db,2,59,"Medium29","000500748589000001700086900302010580000000000067050204004760002200000867876005000");
        insertSudoku(db,2,60,"Medium30","021009008000004031740100025000007086058000170160800000910008052230900000800300410");
        insertFolder(db,3,mContext.getString(R.string.difficulty_hard));
        insertSudoku(db,3,61,"Hard1","600300100071620000805001000500870901009000600407069008000200807000086410008003002");
        insertSudoku(db,3,62,"Hard2","906013008058000090030000010060800920003409100049006030090000080010000670400960301");
        insertSudoku(db,3,63,"Hard3","300060250000500103005210486000380500030000040002045000413052700807004000056070004");
        insertSudoku(db,3,64,"Hard4","060001907100007230080000406018002004070040090900100780607000040051600009809300020");
        insertSudoku(db,3,65,"Hard5","600300208400185000000000450000070835030508020958010000069000000000631002304009006");
        insertSudoku(db,3,66,"Hard6","400030090200001600760800001500318000032000510000592008900003045001700006040020003");
        insertSudoku(db,3,67,"Hard7","004090170900070002007204000043000050798406213060000890000709400600040001085030700");
        insertSudoku(db,3,68,"Hard8","680001003007004000000820000870009204040302080203400096000036000000500400700200065");
        insertSudoku(db,3,69,"Hard9","000002000103400005200050401340005090807000304090300017605030009400008702000100000");
        insertSudoku(db,3,70,"Hard10","050702003073480005000050400040000200027090350006000010005030000400068730700109060");
        insertSudoku(db,3,71,"Hard11","500080020007502801002900040024000308000324000306000470090006700703208900060090005");
        insertSudoku(db,3,72,"Hard12","108090000200308096090000400406009030010205060080600201001000040360904007000060305");
        insertSudoku(db,3,73,"Hard13","010008570607050009052170000001003706070000040803700900000017260100020407024300090");
        insertSudoku(db,3,74,"Hard14","020439800080000001003001520050092703000000000309740080071300900600000030008924010");
        insertSudoku(db,3,75,"Hard15","000500201800006005005207080017960804000000000908074610080405300700600009504009000");
        insertSudoku(db,3,76,"Hard16","920000000500870000038091000052930160090000030073064980000410250000053001000000073");
        insertSudoku(db,3,77,"Hard17","590006010001254709000001400003715008100000004200648100002500000708463900050100047");
        insertSudoku(db,3,78,"Hard18","309870004000005008870400000104580003000706000700034105000009081900300000400057206");
        insertSudoku(db,3,79,"Hard19","800200000910300706000007002084000009095104860100000230500600000609003071000005008");
        insertSudoku(db,3,80,"Hard20","005037001000050627600002530020070000001968200000010090013700008486090000700840100");
        insertSudoku(db,3,81,"Hard21","090350700000800029000402008710000000463508297000000051300204000940005000008037040");
        insertSudoku(db,3,82,"Hard22","000005904080090605006000030030701450008040700074206090060000300801060070309800000");
        insertSudoku(db,3,83,"Hard23","030004087948700500060800009010586720000000000087312050800003070003007865570200090");
        insertSudoku(db,3,84,"Hard24","300687015000030082050000300400300000601050709000004003008000020210040000970521004");
        insertSudoku(db,3,85,"Hard25","702000004030702010400093008000827090007030800080956000300570009020309080600000503");
        insertSudoku(db,3,86,"Hard26","300040057400853060025700000000000430800406001034000000000005690090624003160080002");
        insertSudoku(db,3,87,"Hard27","000260050000005900000380046020094018004000500950810070380021000005700000040058000");
        insertSudoku(db,3,88,"Hard28","062080504008050090700320001000740620000203000027065000200036007040070100803090240");
        insertSudoku(db,3,89,"Hard29","002001000068000003000086090900002086804000102520800009080140000100000920000700500");
        insertSudoku(db,3,90,"Hard30","000030065460950200000086004003070006004090100500010300200140000007065028630020000");
        
        createIndexes(db);
    }
    
    private void insertFolder(SQLiteDatabase db, long folderID, String folderName) {
    	long now = System.currentTimeMillis();
    	db.execSQL("INSERT INTO " + SudokuDatabase.FOLDER_TABLE_NAME + " VALUES ("+ folderID + ", " + now + ", '" + folderName + "');");
    }
    
    // TODO: sudokuName is not used
    private void insertSudoku(SQLiteDatabase db, long folderID, long sudokuID, String sudokuName, String data) {
    	String sql = "INSERT INTO " + SudokuDatabase.SUDOKU_TABLE_NAME + " VALUES ("+sudokuID+", "+ folderID + ", 0, " + SudokuGame.GAME_STATE_NOT_STARTED + ", 0, null, '"+ data + "', null);";
    	db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(TAG, "Upgrading database from version " + oldVersion + " to "
                + newVersion + ".");
        
        createIndexes(db);
    }
    
    private void createIndexes(SQLiteDatabase db) {
    	db.execSQL("create index "+SudokuDatabase.SUDOKU_TABLE_NAME+
     		   "_idx1 on "+
     		   SudokuDatabase.SUDOKU_TABLE_NAME+" ("+SudokuColumns.FOLDER_ID+");");    	
    }
}
